package org.jeecg.modules.oms.parser.impl.confirmation;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.jeecg.modules.oms.entity.ProductSalesOrderDetail;
import org.jeecg.modules.oms.parser.WindowConfirmationFileParser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.beans.factory.annotation.Value;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 新格尔优化软件窗型确认文件解析器
 * 专门用于解析新格尔优化软件导出的窗型确认文件
 */
@Component
public class XinGeErWindowConfirmationFileParser implements WindowConfirmationFileParser {

    private static final Logger log = LoggerFactory.getLogger(XinGeErWindowConfirmationFileParser.class);

    // 线程池配置
    private static final int CORE_POOL_SIZE = Runtime.getRuntime().availableProcessors();
    private static final int MAX_POOL_SIZE = CORE_POOL_SIZE * 2;
    private static final long KEEP_ALIVE_TIME = 60L;

    // 从配置文件中注入上传路径
    @Value("${jeecg.path.upload}")
    private String uploadPath;

    // 初始化线程池
    private static final ExecutorService THREAD_POOL = new ThreadPoolExecutor(
            CORE_POOL_SIZE,
            MAX_POOL_SIZE,
            KEEP_ALIVE_TIME,
            TimeUnit.SECONDS,
            new LinkedBlockingQueue<>(100),
            Executors.defaultThreadFactory(),
            new ThreadPoolExecutor.CallerRunsPolicy()
    );

    /**
     * 图片位置信息类
     */
    private static class ImageLocation {
        private PictureData picture;
        private int startRow;
        private int endRow;
        private int startCol;
        private int endCol;

        public PictureData getPicture() {
            return picture;
        }

        public void setPicture(PictureData picture) {
            this.picture = picture;
        }

        public int getStartRow() {
            return startRow;
        }

        public void setStartRow(int startRow) {
            this.startRow = startRow;
        }

        public int getEndRow() {
            return endRow;
        }

        public void setEndRow(int endRow) {
            this.endRow = endRow;
        }

        public int getStartCol() {
            return startCol;
        }

        public void setStartCol(int startCol) {
            this.startCol = startCol;
        }

        public int getEndCol() {
            return endCol;
        }

        public void setEndCol(int endCol) {
            this.endCol = endCol;
        }
    }

    /**
     * 窗型模块范围类
     */
    private static class WindowModuleRange {
        private ImageLocation imageLocation;
        private int startRow;
        private int endRow;
        private int startCol;
        private int endCol;

        public ImageLocation getImageLocation() {
            return imageLocation;
        }

        public void setImageLocation(ImageLocation imageLocation) {
            this.imageLocation = imageLocation;
        }

        public int getStartRow() {
            return startRow;
        }

        public void setStartRow(int startRow) {
            this.startRow = startRow;
        }

        public int getEndRow() {
            return endRow;
        }

        public void setEndRow(int endRow) {
            this.endRow = endRow;
        }

        public int getStartCol() {
            return startCol;
        }

        public void setStartCol(int startCol) {
            this.startCol = startCol;
        }

        public int getEndCol() {
            return endCol;
        }

        public void setEndCol(int endCol) {
            this.endCol = endCol;
        }
    }

    /**
     * 窗型信息结构定义
     */
    private static class WindowSection {
        int startRow;
        int endRow;
        int startCol;
        int endCol;
        String windowNumber;
        String windowLocation;
        double area;
        int quantity = 1; // 默认数量为1
        Map<String, String> windowData = new HashMap<>();

        public WindowSection(int startRow, int endRow, int startCol, int endCol) {
            this.startRow = startRow;
            this.endRow = endRow;
            this.startCol = startCol;
            this.endCol = endCol;
        }
    }

    /**
     * 解析新格尔优化软件的窗型确认文件
     *
     * @param file     Excel文件
     * @param tenantId 租户ID
     * @return 产品销售订单明细列表
     * @throws IOException 文件读取异常
     * @throws Exception   其他解析异常
     */
    @Override
    public List<ProductSalesOrderDetail> parseFile(File file, Integer tenantId) throws IOException, Exception {
        List<ProductSalesOrderDetail> detailList = new ArrayList<>();
        
        try (InputStream is = new FileInputStream(file)) {
            Workbook workbook;
            
            // 根据文件后缀选择不同的Workbook实现
            if (file.getName().toLowerCase().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else if (file.getName().toLowerCase().endsWith(".xls")) {
                workbook = new HSSFWorkbook(is);
            } else {
                throw new IllegalArgumentException("不支持的文件格式，仅支持.xlsx和.xls格式");
            }
            
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            
            // 1. 解析订单基本信息
            Map<String, Object> orderInfo = extractOrderInfo(sheet);
            log.info("订单基本信息: {}", orderInfo);

            // 2. 查找所有图片位置
            List<ImageLocation> imageLocations = findAllPicturesLocation(sheet);
            log.info("找到 {} 个图片", imageLocations.size());

            // 3. 查找窗型模块范围
            List<WindowModuleRange> windowModuleRanges = new ArrayList<>();
            for (ImageLocation imageLocation : imageLocations) {
                WindowModuleRange range = findWindowModuleRange(sheet, imageLocation);
                if (range != null) {
                    windowModuleRanges.add(range);
                }
            }
            log.info("找到 {} 个窗型模块", windowModuleRanges.size());

            // 4. 如果通过图片找到了窗型模块，则优先使用图片解析方式
            if (!windowModuleRanges.isEmpty()) {
                // 使用线程池并行解析窗型信息
                List<Future<ProductSalesOrderDetail>> futures = new ArrayList<>();
                for (WindowModuleRange range : windowModuleRanges) {
                    futures.add(THREAD_POOL.submit(() -> processWindowModuleRange(sheet, range, tenantId, orderInfo)));
                }

                // 收集所有线程的处理结果
                for (Future<ProductSalesOrderDetail> future : futures) {
                    try {
                        ProductSalesOrderDetail detail = future.get();
                        if (detail != null) {
                            detailList.add(detail);
                        }
                    } catch (InterruptedException | ExecutionException e) {
                        log.error("解析窗型信息时发生错误", e);
                    }
                }
            } else {
                // 5. 如果没有找到窗型图片，则使用原始的文本解析方式
                // 定位并提取所有窗型部分
                List<WindowSection> windowSections = locateWindowSections(sheet);
                log.info("找到 {} 个窗型部分", windowSections.size());

                // 使用线程池并行解析窗型信息
                List<Future<ProductSalesOrderDetail>> futures = new ArrayList<>();
                for (WindowSection section : windowSections) {
                    futures.add(THREAD_POOL.submit(() -> processWindowSection(sheet, section, tenantId, orderInfo)));
                }

                // 收集所有线程的处理结果
                for (Future<ProductSalesOrderDetail> future : futures) {
                    try {
                        ProductSalesOrderDetail detail = future.get();
                        if (detail != null) {
                            detailList.add(detail);
                        }
                    } catch (InterruptedException | ExecutionException e) {
                        log.error("解析窗型信息时发生错误", e);
                    }
                }
            }

            workbook.close();
            if(detailList.size() > 0){
                HashMap<String, Object> extendMap = new HashMap<>();
                extendMap.put("windowInfo",orderInfo);
                detailList.get(0).setExtendMap(extendMap);
            }
        }

        return detailList;
    }

    /**
     * 在Excel中查找所有图片的位置信息，排除客户信息区域的图片
     */
    private List<ImageLocation> findAllPicturesLocation(Sheet sheet) {
        List<ImageLocation> imageLocations = new ArrayList<>();

        if (!(sheet instanceof XSSFSheet)) {
            return imageLocations;
        }

        XSSFSheet xssfSheet = (XSSFSheet) sheet;
        if (xssfSheet.getDrawingPatriarch() == null) {
            return imageLocations;
        }

        // 首先查找"客户名称:"所在的列
        int customerInfoColumn = -1;
        // 从第一行开始查找
        for (int i = 0; i <= 20; i++) { // 限制查找范围为前20行
                Row row = sheet.getRow(i);
            if (row == null) continue;

            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    String value = getCellValueAsString(cell);
                    if (value.contains("客户名称:") || value.contains("客户名称：")) {
                        customerInfoColumn = j;
                        log.debug("找到客户信息列: {}, 行: {}", customerInfoColumn, i);
                        break;
                    }
                }
            }
            if (customerInfoColumn != -1) break;
        }

        log.debug("开始查找图片，客户信息列: {}", customerInfoColumn);

        // 获取所有图片，但排除客户信息区域的图片
        List<XSSFShape> shapes = xssfSheet.getDrawingPatriarch().getShapes();
        int totalShapes = shapes.size();
        int excludedShapes = 0;

        for (XSSFShape shape : shapes) {
            if (shape instanceof XSSFPicture) {
                XSSFPicture picture = (XSSFPicture) shape;
                XSSFClientAnchor anchor = picture.getPreferredSize();

                // 如果找到了客户信息列，并且图片起始列位置大于等于客户信息列，则排除该图片
                if (customerInfoColumn != -1 && anchor.getCol1() >= customerInfoColumn) {
                    log.debug("排除客户信息区域图片: 位置 行({}-{}), 列({}-{})",
                            anchor.getRow1(), anchor.getRow2(),
                            anchor.getCol1(), anchor.getCol2());
                    excludedShapes++;
                    continue;
                }
                
                ImageLocation location = new ImageLocation();
                location.setPicture(picture.getPictureData());
                location.setStartRow(anchor.getRow1());
                location.setEndRow(anchor.getRow2());
                location.setStartCol(anchor.getCol1());
                location.setEndCol(anchor.getCol2());

                log.debug("添加潜在窗型图片: 位置 行({}-{}), 列({}-{})",
                        anchor.getRow1(), anchor.getRow2(),
                        anchor.getCol1(), anchor.getCol2());

                imageLocations.add(location);
            }
        }

        log.info("找到 {} 个图片，排除 {} 个客户信息区域图片，剩余 {} 个潜在窗型图片",
                totalShapes, excludedShapes, imageLocations.size());

        return imageLocations;
    }

    /**
     * 判断是否是窗型图片并获取其信息范围
     *
     * @param sheet         Excel工作表
     * @param imageLocation 图片位置信息
     * @return 窗型模块范围对象，如果不是窗型图片则返回null
     */
    private WindowModuleRange findWindowModuleRange(Sheet sheet, ImageLocation imageLocation) {
        try {
            // 首先检查是否包含"客户地址："，如果包含则该列所有图片都不是窗型图片
            int currentRow = imageLocation.getEndRow();
            while (currentRow <= sheet.getLastRowNum()) {
                Row row = sheet.getRow(currentRow);
                if (row != null) {
                    Cell cell = row.getCell(imageLocation.getStartCol());
                    if (cell != null && getCellValueAsString(cell).trim().contains("客户地址")) {
                        log.debug("检测到客户地址标记，该列图片非窗型图片");
                        return null;
                    }
                }
                currentRow++;
            }

            // 从图片下方开始查找窗号标记
            currentRow = imageLocation.getEndRow();
            int targetCol = imageLocation.getStartCol() - 1; // 目标列为图片所在列-1
            boolean foundWindowNumber = false;
            int windowInfoStartRow = -1;

            while (currentRow <= Math.min(sheet.getLastRowNum(), imageLocation.getEndRow() + 20)) {
                Row row = sheet.getRow(currentRow);
                if (row != null) {
                    Cell cell = row.getCell(targetCol);
                    if (cell != null) {
                        String cellValue = getCellValueAsString(cell).trim();

                        // 找到窗号标记，记录开始行
                        if (cellValue.equals("窗号")) {
                            foundWindowNumber = true;
                            windowInfoStartRow = currentRow;
                            break;
                        }
                    }
                }
                currentRow++;
            }

            // 如果没有找到窗号标记，说明不是窗型图片
            if (!foundWindowNumber) {
                log.debug("未找到窗号标记，该图片非窗型图片");
                return null;
            }

            // 从窗号标记开始向下查找备注标记
            currentRow = windowInfoStartRow;
            int windowInfoEndRow = -1;

            while (currentRow <= sheet.getLastRowNum()) {
                Row row = sheet.getRow(currentRow);
                if (row != null) {
                    Cell cell = row.getCell(targetCol);
                    if (cell != null && getCellValueAsString(cell).trim().equals("备注")) {
                        windowInfoEndRow = currentRow;
                        break;
                    }
                }
                currentRow++;
            }

            // 如果找到了完整的窗型信息范围，创建并返回WindowModuleRange对象
            if (windowInfoEndRow != -1) {
                WindowModuleRange range = new WindowModuleRange();
                range.setImageLocation(imageLocation);
                range.setStartRow(imageLocation.getStartRow()); // 范围的开始行是图片的开始行
                range.setEndRow(windowInfoEndRow); // 范围的结束行是窗型信息的结束行
                range.setStartCol(targetCol);
                range.setEndCol(imageLocation.getEndCol());

                log.debug("找到窗型信息范围: 图片开始行={}, 窗型信息结束行={}, 窗型信息列={}",
                        imageLocation.getStartRow(), windowInfoEndRow, targetCol);
                return range;
            }

            log.debug("未找到完整的窗型信息范围");
            return null;

        } catch (Exception e) {
            log.error("判断窗型图片失败", e);
            return null;
        }
    }

    /**
     * 处理窗型模块范围，提取详细信息
     */
    private ProductSalesOrderDetail processWindowModuleRange(Sheet sheet, WindowModuleRange range, Integer tenantId, Map<String, Object> orderInfo) {
                ProductSalesOrderDetail detail = new ProductSalesOrderDetail();
        try {
                detail.setId(UUID.randomUUID().toString().replace("-", ""));
                detail.setTenantId(tenantId);
                
            // 提取窗型信息
            Map<String, String> windowData = new HashMap<>();
            int quantity = 1; // 默认数量为1
            
            // 存储关键字段的位置信息
            Map<String, int[]> fieldPositions = new HashMap<>();
            
            // 第一步：扫描整个范围，找到关键字段的位置
            for (int i = range.getStartRow(); i <= range.getEndRow(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                
                for (int j = range.getStartCol(); j <= range.getEndCol(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) continue;
                    
                    String cellValue = getCellValueAsString(cell).trim();
                    if (cellValue.isEmpty()) continue;
                    
                    // 检查是否是关键字段标签
                    if (cellValue.equals("窗号") || 
                        cellValue.equals("系列") || 
                        cellValue.equals("颜色") || 
                        cellValue.equals("位置") || 
                        cellValue.equals("备注")) {
                        
                        // 记录字段位置 [行, 列]
                        fieldPositions.put(cellValue, new int[]{i, j});
                        log.debug("找到字段 {} 位置: 行={}, 列={}", cellValue, i, j);
                    }
                }
            }
            
            // 第二步：根据找到的字段位置，提取对应的数据
            for (Map.Entry<String, int[]> entry : fieldPositions.entrySet()) {
                String fieldName = entry.getKey();
                int[] position = entry.getValue();
                int row = position[0];
                int col = position[1];
                
                // 获取该行对象
                Row dataRow = sheet.getRow(row);
                if (dataRow == null) continue;
                
                // 从字段列的下一列开始查找第一个非空值
                String fieldValue = null;
                for (int j = col + 1; j <= range.getEndCol(); j++) {
                    Cell valueCell = dataRow.getCell(j);
                    if (valueCell == null) continue;
                    
                    String valueStr = getCellValueAsString(valueCell).trim();
                    if (!valueStr.isEmpty()) {
                        fieldValue = valueStr;
                        log.debug("字段 {} 的值: {}", fieldName, fieldValue);
                        break;
                    }
                }
                
                // 如果找到了值，根据字段类型处理
                if (fieldValue != null) {
                    switch (fieldName) {
                        case "窗号":
                            // 解析窗号中的数量和面积信息
                            String[] parts = fieldValue.split("/");
                            if (parts.length >= 3) {
                                detail.setWindowNo(parts[0]);

                                // 处理数量部分
                                try {
                                    quantity = Integer.parseInt(parts[1].replaceAll("[^0-9]", ""));
                                } catch (NumberFormatException e) {
                                    log.error("解析数量时发生错误: {}", parts[1], e);
                                }
                                
                                // 改进的面积提取逻辑
                                String areaRegex = "\\d+\\.\\d+";  // 匹配小数数字
                                Pattern areaPattern = Pattern.compile(areaRegex);
                                Matcher areaMatcher = areaPattern.matcher(parts[2]);
                                
                                if (areaMatcher.find()) {  // 必须先调用find()
                                    detail.setTotalArea(new BigDecimal(areaMatcher.group()));
                } else {
                                    // 备用方案：移除所有非数字字符
                                    String cleanNumber = parts[2].replaceAll("[^0-9.]", "");
                                    if (!cleanNumber.isEmpty()) {
                                        try {
                                            detail.setTotalArea(new BigDecimal(cleanNumber));
                                        } catch (NumberFormatException e) {
                                            log.error("解析面积时发生错误: {}", cleanNumber, e);
                                        }
                                    }
                                }
                            } else {
                                // 如果窗号不是标准格式，尝试使用parseWindowNoInfo方法解析
                                Map<String, Object> windowInfo = parseWindowNoInfo(fieldValue);
                                if ((int) windowInfo.get("quantity") > 1) {
                                    quantity = (int) windowInfo.get("quantity");
                                }
                                
                                double area = (double) windowInfo.get("area");
                                if (area > 0 && detail.getTotalArea() == null) {
                                    detail.setTotalArea(BigDecimal.valueOf(area));
                                }
                            }
                            break;
                            
                        case "系列":
                            detail.setWindowSeries(fieldValue);
                            break;
                            
                        case "颜色":
                            detail.setWindowColor(fieldValue);
                            break;
                            
                        case "位置":
                            detail.setInstallationLocation(fieldValue);
                            detail.setInstallLocation(fieldValue);
                            break;
                            
                        case "备注":
                            detail.setRemark(fieldValue);
                            break;
                    }
                    
                    // 保存到窗型数据中
                    windowData.put(fieldName, fieldValue);
                    }
                }
                
                // 设置数量
            detail.setQuantity(quantity);
            
            // 获取订单编号
            String orderId = orderInfo.containsKey("orderNo") ? orderInfo.get("orderNo").toString() : null;
            String windowNo = detail.getWindowNo();
            
            // 从窗型信息范围内查找并保存图片
            boolean imageSaved = false;
            if (sheet instanceof XSSFSheet) {
                XSSFSheet xssfSheet = (XSSFSheet) sheet;
                if (xssfSheet.getDrawingPatriarch() != null) {
                    log.debug("在窗型范围内查找图片: 行({}-{}), 列({}-{})", 
                             range.getStartRow(), range.getEndRow(),
                             range.getStartCol(), range.getEndCol());
                             
                    List<XSSFShape> shapes = xssfSheet.getDrawingPatriarch().getShapes();
                    for (XSSFShape shape : shapes) {
                        if (shape instanceof XSSFPicture) {
                            XSSFPicture picture = (XSSFPicture) shape;
                            XSSFClientAnchor anchor = picture.getPreferredSize();
                            
                            // 判断图片是否在窗型范围内
                            // 图片和窗型信息的起始行可能不同，但我们考虑图片位于窗型信息范围内的情况
                            if ((anchor.getRow1() >= range.getStartRow() && anchor.getRow1() <= range.getEndRow()) ||
                                (anchor.getRow2() >= range.getStartRow() && anchor.getRow2() <= range.getEndRow())) {
                                
                                log.debug("在窗型范围内找到图片: 图片位置 行({}-{}), 列({}-{})", 
                                         anchor.getRow1(), anchor.getRow2(),
                                         anchor.getCol1(), anchor.getCol2());
                                
                                // 获取图片数据
                                PictureData pictureData = picture.getPictureData();
                                
                                // 检查图片大小，限制为10MB
                                byte[] imageData = pictureData.getData();
                                if (imageData.length > 10 * 1024 * 1024) {
                                    log.warn("图片大小超过限制(10MB)，跳过处理: {} bytes", imageData.length);
                                    continue;
                                }
                                
                                // 生成图片ID
                                String imageId = UUID.randomUUID().toString().replace("-", "");
                                
                                // 保存图片文件并获取访问URL
                                String url = saveImageFile(pictureData, imageId, windowNo, orderId);
                                
                                if (url != null && !url.isEmpty()) {
                                    detail.setPhotoUrl(url);
                                    log.info("成功保存窗型图片，URL: {}", url);
                                    imageSaved = true;
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            
            // 如果在窗型范围内未找到图片，尝试使用原来的imageLocation
            if (!imageSaved && range.getImageLocation() != null && range.getImageLocation().getPicture() != null) {
                log.debug("窗型范围内未找到合适的图片，尝试使用原始图片位置");
                
                // 使用extractPhotoUrl方法获取图片URL
                String photoUrl = extractPhotoUrl(sheet, range.getImageLocation(), windowNo, orderId);
                if (photoUrl != null && !photoUrl.isEmpty()) {
                    detail.setPhotoUrl(photoUrl);
                    log.debug("使用原始图片位置成功保存图片，URL: {}", photoUrl);
                } else {
                    // 如果没有找到图片或保存失败，不设置photoUrl
                    detail.setPhotoUrl(null);
                    log.warn("未能保存任何窗型图片，photoUrl设置为null");
                }
            } else if (!imageSaved) {
                log.warn("未能找到任何可用的窗型图片");
            }
            
            // 使用订单基本信息填充其他字段
            if (orderInfo.containsKey("orderNo")) {
                detail.setOrderId(orderInfo.get("orderNo").toString());
            }
            
            // 设置默认单价和金额
            if (detail.getUnitPrice() == null) {
                detail.setUnitPrice(BigDecimal.ZERO);
            }
            
            if (detail.getAmount() == null && detail.getUnitPrice() != null) {
                BigDecimal quantityDecimal = BigDecimal.valueOf(quantity);
                detail.setAmount(detail.getUnitPrice().multiply(quantityDecimal).setScale(2, RoundingMode.HALF_UP));
            }
            
            return detail;
            
        } catch (Exception e) {
            log.error("处理窗型模块范围时发生错误", e);
            return null;
        }
    }

    /**
     * 提取窗型图片URL (用于ImageLocation)
     *
     * @param sheet         工作表
     * @param imageLocation 图片位置
     * @param windowNo      窗号
     * @param orderId       订单ID
     * @return 图片URL
     */
    private String extractPhotoUrl(Sheet sheet, ImageLocation imageLocation, String windowNo, String orderId) {
        // 如果是XSSFSheet（XLSX格式），尝试提取图片
        if (sheet instanceof XSSFSheet) {
            try {
                log.debug("开始从XLSX格式提取窗型图片, 窗号: {}, 订单号: {}", windowNo, orderId);
                XSSFSheet xssfSheet = (XSSFSheet) sheet;
                if (xssfSheet.getDrawingPatriarch() != null) {
                    List<XSSFShape> shapes = xssfSheet.getDrawingPatriarch().getShapes();
                    log.debug("找到 {} 个形状对象", shapes.size());

                    for (XSSFShape shape : shapes) {
                        if (shape instanceof XSSFPicture) {
                            XSSFPicture picture = (XSSFPicture) shape;
                            XSSFClientAnchor anchor = picture.getPreferredSize();

                            // 判断图片是否在窗型区域内
                            if (anchor.getRow1() >= imageLocation.getStartRow() - 5 &&
                                    anchor.getRow1() <= imageLocation.getEndRow() + 5 &&
                                    anchor.getCol1() >= imageLocation.getStartCol() - 5 &&
                                    anchor.getCol1() <= imageLocation.getEndCol() + 5) {

                                log.debug("找到图片，位置范围: 行({}-{}), 列({}-{})",
                                        anchor.getRow1(), anchor.getRow2(),
                                        anchor.getCol1(), anchor.getCol2());

                                // 获取图片数据
                                PictureData pictureData = picture.getPictureData();

                                // 检查图片大小，限制为10MB
                                byte[] imageData = pictureData.getData();
                                if (imageData.length > 10 * 1024 * 1024) {
                                    log.warn("图片大小超过限制(10MB)，跳过处理: {} bytes", imageData.length);
                                    continue; // 尝试查找下一个符合条件的图片
                                }

                                // 生成图片ID，可以是UUID或者其他唯一标识
                                String imageId = UUID.randomUUID().toString().replace("-", "");

                                // 保存图片文件并获取访问URL
                                String url = saveImageFile(pictureData, imageId, windowNo, orderId);

                                if (url != null && !url.isEmpty()) {
                                    log.info("成功提取窗型图片，URL: {}", url);
                                    return url;
                                } else {
                                    log.warn("保存图片失败，尝试下一个图片");
                                }
                            }
                        }
                    }
                    log.warn("未找到位于窗型区域内的有效图片");
                } else {
                    log.warn("工作表中没有绘图元素");
                }
            } catch (Exception e) {
                log.error("提取窗型图片时发生错误", e);
            }
        } else {
            log.warn("不支持的工作表类型，只能从XLSX格式提取图片");
        }

        return null;
    }

    /**
     * 提取窗型图片URL (用于WindowSection)
     */
    private String extractPhotoUrl(Sheet sheet, WindowSection section) {
        // 如果是XSSFSheet（XLSX格式），尝试提取图片
        if (sheet instanceof XSSFSheet) {
            try {
                XSSFSheet xssfSheet = (XSSFSheet) sheet;
                if (xssfSheet.getDrawingPatriarch() != null) {
                    List<XSSFShape> shapes = xssfSheet.getDrawingPatriarch().getShapes();
                    for (XSSFShape shape : shapes) {
                        if (shape instanceof XSSFPicture) {
                            XSSFPicture picture = (XSSFPicture) shape;
                            XSSFClientAnchor anchor = picture.getPreferredSize();

                            // 判断图片是否在窗型区域内
                            if (anchor.getRow1() >= section.startRow - 5 &&
                                    anchor.getRow1() <= section.endRow + 5 &&
                                    anchor.getCol1() >= section.startCol - 5 &&
                                    anchor.getCol1() <= section.endCol + 5) {

                                // 获取图片数据
                                PictureData pictureData = picture.getPictureData();

                                // 检查图片大小，限制为10MB
                                byte[] imageData = pictureData.getData();
                                if (imageData.length > 10 * 1024 * 1024) {
                                    log.warn("图片大小超过限制(10MB)，跳过处理: {} bytes", imageData.length);
                                    return null;
                                }

                                // 生成图片ID，可以是UUID或者其他唯一标识
                                String imageId = UUID.randomUUID().toString().replace("-", "");

                                // 这里应该有实际保存图片文件的逻辑，并返回访问URL
                                String url = saveImageFile(pictureData, imageId, section.windowNumber, section.windowData.get("orderNo"));

                                log.debug("找到窗型图片: {}", url);
                                return url;
                            }
                        }
                    }
                }
            } catch (Exception e) {
                log.error("提取窗型图片时发生错误", e);
            }
        }

        return null;
    }

    /**
     * 保存图片文件并返回URL
     *
     * @param pictureData 图片数据
     * @param imageId     图片ID
     * @param windowNo    窗号
     * @param orderId     订单编号
     * @return 图片URL
     */
    private String saveImageFile(PictureData pictureData, String imageId, String windowNo, String orderId) {
        try {
            // 业务路径，与CommonController中一致
            String bizPath = "windowImages";
            
            // 从配置中获取上传根目录路径
            log.debug("从配置文件获取的上传路径: {}", this.uploadPath);
            
            // 检查上传路径是否为空
            if(this.uploadPath == null || this.uploadPath.trim().isEmpty()) {
                log.error("未配置上传路径，请检查application-*.yml中的jeecg.path.upload配置");
                return null;
            }
            
            // 构建完整的文件保存路径
            String fullUploadPath = this.uploadPath + File.separator + bizPath;
            log.info("图片保存完整路径: {}", fullUploadPath);

            // 确保目录存在
            File dir = new File(fullUploadPath);
            if (!dir.exists()) {
                boolean created = dir.mkdirs();
                if (!created) {
                    log.error("创建上传目录失败: {}", fullUploadPath);
                    return null;
                }
                log.info("成功创建上传目录: {}", fullUploadPath);
            }

            // 根据MIME类型确定文件扩展名
            String extension = getImageExtension(pictureData.getMimeType());

            // 过滤窗号和订单号中的非法字符
            String safeWindowNo = windowNo != null ? windowNo.replaceAll("[\\\\/:*?\"<>|]", "_") : "unknown";
            String safeOrderId = orderId != null ? orderId.replaceAll("[\\\\/:*?\"<>|]", "_") : "unknown";

            // 构建文件名：订单编号_窗号_时间戳.扩展名 (使用单下划线作为分隔符)
            String fileName = safeOrderId + "_" + safeWindowNo + "_" + System.currentTimeMillis() + "." + extension;
            String filePath = fullUploadPath + File.separator + fileName;
            log.debug("正在保存图片到: {}", filePath);

            // 检查文件是否已存在
            File file = new File(filePath);
            if (file.exists()) {
                log.warn("文件已存在，将被覆盖: {}", filePath);
            }

            // 将图片数据写入文件
            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                fos.write(pictureData.getData());
                fos.flush();
            }

            // 确认文件是否成功写入
            if (!new File(filePath).exists()) {
                log.error("文件写入失败，无法找到保存的文件: {}", filePath);
                return null;
            }

            log.info("图片成功保存到: {}", filePath);
            
            // 返回符合CommonController格式的相对URL路径
            // 格式应与 sys/common/static/{业务路径}/{文件名} 一致
            String relativePath = bizPath + "/" + fileName;
            log.info("图片访问相对路径: {}", relativePath);

            return relativePath;

        } catch (Exception e) {
            log.error("保存图片文件失败", e);
            // 如果保存失败，返回null
            return null;
        }
    }

    /**
     * 根据MIME类型获取图片扩展名
     */
    private String getImageExtension(String mimeType) {
        if (mimeType == null) {
            return "png";
        }

        switch (mimeType.toLowerCase()) {
            case "image/jpeg":
            case "image/jpg":
                return "jpg";
            case "image/png":
                return "png";
            case "image/gif":
                return "gif";
            case "image/bmp":
                return "bmp";
            case "image/tiff":
                return "tiff";
            default:
                return "png";
        }
    }

    /**
     * 从Excel表格中提取订单基本信息
     */
    private Map<String, Object> extractOrderInfo(Sheet sheet) {
        Map<String, Object> orderInfo = new HashMap<>();

        try {
            // 查找订单编号 - 通常在红框中的单元格
            for (int i = 0; i < 10; i++) { // 限制在前10行中查找
            Row row = sheet.getRow(i);
                if (row == null) continue;
                
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) continue;
                    
                    String cellValue = getCellValueAsString(cell);
                    // 查找可能是订单编号的单元格 - 通常以"FG"开头或包含有意义的订单号格式
                    if (cellValue.startsWith("FG") || cellValue.matches(".*\\d{8,}.*")) {
                        orderInfo.put("orderNo", cellValue.trim());
                        log.debug("找到订单编号: {}", cellValue);
                        break;
                    }
                }
                
                if (orderInfo.containsKey("orderNo")) break;
            }

            // 查找创建时间 - 通常在包含"创建时间:"的单元格的右侧
            for (int i = 0; i < 20; i++) { // 限制在前20行中查找
                Row row = sheet.getRow(i);
                if (row == null) continue;
            
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                    if (cell == null) continue;
                    
                    String cellValue = getCellValueAsString(cell);
                    if (cellValue.contains("创建时间")) {
                        // 创建数据格式为 创建时间: 2025-03-17  截取时间字符串
                        String dateStr = cellValue.substring(cellValue.indexOf(":") + 1).trim();
                        orderInfo.put("orderDate", dateStr);

                        
                        if (orderInfo.containsKey("orderDate")) break;
                    }
                }
                
                if (orderInfo.containsKey("orderDate")) break;
            }
            
            // 如果仍然没有找到订单日期，设置默认值为当前日期
            if (!orderInfo.containsKey("orderDate")) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                orderInfo.put("orderDate", sdf.format(new Date()));
                log.info("未找到订单日期，使用当前日期: {}", orderInfo.get("orderDate"));
            }

            // 记录最终结果
            log.info("订单信息提取结果: orderNo={}, orderDate={}", 
                    orderInfo.getOrDefault("orderNo", "未找到"), 
                    orderInfo.get("orderDate"));
            
        } catch (Exception e) {
            log.error("解析订单基本信息时发生错误", e);
            
            // 确保即使发生异常，也能返回默认的订单日期
            if (!orderInfo.containsKey("orderDate")) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                orderInfo.put("orderDate", sdf.format(new Date()));
            }
        }

        return orderInfo;
    }

    /**
     * 检查字符串是否符合日期格式
     */
    private boolean isDateFormat(String dateStr) {
        if (dateStr == null || dateStr.trim().isEmpty()) {
            return false;
        }
        
        // 移除所有非数字和常见日期分隔符
        String cleanDateStr = dateStr.trim().replaceAll("[^0-9/\\-.]", "");
        if (cleanDateStr.isEmpty()) {
            return false;
        }
        
        // 检查是否包含至少一个分隔符
        return cleanDateStr.contains("/") || cleanDateStr.contains("-") || cleanDateStr.contains(".");
    }

    /**
     * 在工作表中定位所有窗型部分
     */
    private List<WindowSection> locateWindowSections(Sheet sheet) {
        List<WindowSection> sections = new ArrayList<>();

        try {
            // 遍历工作表查找窗型信息的特征标记
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) continue;

                String cellValue = getCellValueAsString(cell);
                    // 窗型标识特征，如"C1/1楼/2.16㎡" 或 "窗号"
                    if ((cellValue.contains("/") && (cellValue.contains("㎡") || cellValue.contains("m²"))) ||
                            cellValue.matches("(?i)C\\d+.*") || cellValue.matches("(?i)(窗|门)\\d+.*")) {

                        // 检测到窗型信息区域的起始
                        WindowSection section = detectWindowSection(sheet, i, j);
                        if (section != null) {
                            sections.add(section);
                            // 跳过已处理的区域
                            i = section.endRow;
                            break;
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.error("定位窗型部分时发生错误", e);
        }

        return sections;
    }

    /**
     * 从检测到窗型标识的位置开始，确定完整的窗型信息区域
     */
    private WindowSection detectWindowSection(Sheet sheet, int startRowIndex, int startColIndex) {
        try {
            // 获取窗号单元格的值
            Cell windowNoCell = sheet.getRow(startRowIndex).getCell(startColIndex);
            String windowNoValue = getCellValueAsString(windowNoCell);

            // 查找区域的结束行
            int endRowIndex = startRowIndex;
            while (endRowIndex < sheet.getLastRowNum()) {
                Row nextRow = sheet.getRow(endRowIndex + 1);
                if (nextRow == null) break;

                boolean hasContent = false;
                for (int col = 0; col < 20; col++) {  // 假设窗型信息不超过20列
                    Cell cell = nextRow.getCell(col);
                    if (cell != null && !getCellValueAsString(cell).trim().isEmpty()) {
                        hasContent = true;
                        break;
                    }
                }

                if (!hasContent) break;

                // 检查是否包含窗型属性信息(如系列、颜色等)
                boolean foundWindowProperty = false;
                for (int col = 0; col < 10; col++) {
                    Cell cell = nextRow.getCell(col);
                    if (cell != null) {
                        String value = getCellValueAsString(cell);
                        if (value.contains("系列") || value.contains("颜色") || value.contains("玻璃") ||
                                value.contains("位置") || value.contains("五金") || value.contains("纱网")) {
                            foundWindowProperty = true;
                            break;
                        }
                    }
                }

                // 检查是否已经找到了下一个窗型区域的开始
                boolean foundNextWindow = false;
                for (int col = 0; col < 10; col++) {
                    Cell cell = nextRow.getCell(col);
                    if (cell != null) {
                        String value = getCellValueAsString(cell);
                        if (value.matches("(?i)C\\d+.*") ||
                                (value.contains("/") && (value.contains("㎡") || value.contains("m²")))) {
                            foundNextWindow = true;
                            break;
                        }
                    }
                }

                if (foundNextWindow) break;

                if (foundWindowProperty) {
                    endRowIndex++;
                } else if (endRowIndex - startRowIndex >= 2) {  // 防止过早结束
                    endRowIndex++;
                } else {
                    break;
                }
            }

            // 查找区域的结束列
            int endColIndex = startColIndex;
            for (int i = startRowIndex; i <= endRowIndex; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    int lastCol = row.getLastCellNum() - 1;
                    endColIndex = Math.max(endColIndex, lastCol);
                }
            }

            // 创建窗型区域
            WindowSection section = new WindowSection(startRowIndex, endRowIndex, startColIndex, endColIndex);
            extractWindowInfo(section, windowNoValue);

            return section;

        } catch (Exception e) {
            log.error("检测窗型区域时发生错误", e);
            return null;
        }
    }

    /**
     * 从窗号单元格值中提取窗型信息
     */
    private void extractWindowInfo(WindowSection section, String windowNoValue) {
        try {
            // 从窗号值中解析信息
            if (windowNoValue != null) {
                // 使用统一的窗号解析方法
                Map<String, Object> windowInfo = parseWindowNoInfo(windowNoValue);

                // 设置窗号
                section.windowNumber = (String) windowInfo.get("windowNo");

                // 设置数量
                section.quantity = (int) windowInfo.get("quantity");
                section.windowData.put("quantity", String.valueOf(section.quantity));

                // 设置面积
                section.area = (double) windowInfo.get("area");
                if (section.area > 0) {
                    section.windowData.put("totalArea", String.valueOf(section.area));
                }

                // 如果没有获取到位置信息，尝试从窗号值中提取
                if ((section.windowLocation == null || section.windowLocation.isEmpty()) && windowNoValue.contains("/")) {
                    Pattern locationPattern = Pattern.compile("/([^/]+)/");
                    Matcher locationMatcher = locationPattern.matcher(windowNoValue);
                    if (locationMatcher.find()) {
                        section.windowLocation = locationMatcher.group(1);
                        section.windowData.put("位置", section.windowLocation);
                    }
                }
            }
        } catch (Exception e) {
            log.error("提取窗型信息时发生错误: {}", windowNoValue, e);
        }
    }

    /**
     * 处理一个窗型区域，提取详细信息
     */
    private ProductSalesOrderDetail processWindowSection(Sheet sheet, WindowSection section, Integer tenantId, Map<String, Object> orderInfo) {
        ProductSalesOrderDetail detail = new ProductSalesOrderDetail();
        try {
            detail.setId(UUID.randomUUID().toString().replace("-", ""));
            detail.setTenantId(tenantId);

            // 设置窗号
            detail.setWindowNo(section.windowNumber);

            // 提取各种属性
            for (int i = section.startRow; i <= section.endRow; i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                for (int j = section.startCol; j <= Math.min(section.endCol, row.getLastCellNum() - 1); j++) {
                    Cell labelCell = row.getCell(j);
                    if (labelCell == null) continue;

                    String labelValue = getCellValueAsString(labelCell);
                    if (labelValue.contains("系列") || labelValue.equals("系列")) {
                        if (j + 1 <= section.endCol) {
                            Cell valueCell = row.getCell(j + 1);
                            if (valueCell != null) {
                                String seriesValue = getCellValueAsString(valueCell);
                                detail.setWindowSeries("新格尔-" + seriesValue);
                                section.windowData.put("系列", seriesValue);
                            }
                        }
                    } else if (labelValue.contains("颜色") || labelValue.equals("颜色")) {
                        if (j + 1 <= section.endCol) {
                            Cell valueCell = row.getCell(j + 1);
                            if (valueCell != null) {
                                String colorValue = getCellValueAsString(valueCell);
                                detail.setWindowColor(colorValue);
                                section.windowData.put("颜色", colorValue);
                            }
                        }
                    } else if (labelValue.contains("位置") || labelValue.equals("位置")) {
                        if (j + 1 <= section.endCol) {
                            Cell valueCell = row.getCell(j + 1);
                            if (valueCell != null) {
                                String locationValue = getCellValueAsString(valueCell);
                                detail.setInstallationLocation(locationValue);
                                detail.setInstallLocation(locationValue);
                                section.windowData.put("位置", locationValue);

                                // 如果之前没有获取到位置信息，使用这个值
                                if (section.windowLocation == null || section.windowLocation.isEmpty()) {
                                    section.windowLocation = locationValue;
                                }
                            }
                        }
                    } else if (labelValue.contains("玻璃") || labelValue.equals("玻璃")) {
                        if (j + 1 <= section.endCol) {
                            Cell valueCell = row.getCell(j + 1);
                            if (valueCell != null) {
                                section.windowData.put("玻璃", getCellValueAsString(valueCell));
                            }
                        }
                    } else if (labelValue.contains("五金") || labelValue.equals("五金")) {
                        if (j + 1 <= section.endCol) {
                            Cell valueCell = row.getCell(j + 1);
                            if (valueCell != null) {
                                section.windowData.put("五金", getCellValueAsString(valueCell));
                            }
                        }
                    } else if (labelValue.contains("纱网") || labelValue.equals("纱网")) {
                        if (j + 1 <= section.endCol) {
                            Cell valueCell = row.getCell(j + 1);
                            if (valueCell != null) {
                                section.windowData.put("纱网", getCellValueAsString(valueCell));
                            }
                        }
                    }
                }
            }

            // 设置数量，优先使用从窗号解析出的数量
            if (section.windowData.containsKey("quantity")) {
                try {
                    int quantity = Integer.parseInt(section.windowData.get("quantity"));
                    detail.setQuantity(quantity);
                } catch (NumberFormatException e) {
                    detail.setQuantity(1); // 默认数量为1
                }
            } else {
                detail.setQuantity(1); // 默认数量为1
            }

            // 设置面积
            if (section.area > 0) {
                BigDecimal totalArea = BigDecimal.valueOf(section.area);
                detail.setTotalArea(totalArea);

                // 计算单窗面积 = 总面积 / 数量
                int quantity = detail.getQuantity() != null ? detail.getQuantity() : 1;
                if (quantity > 0) {
                    BigDecimal singleArea = totalArea.divide(BigDecimal.valueOf(quantity), 2, RoundingMode.HALF_UP);
                    detail.setSingleWindowArea(singleArea);
                    detail.setArea(singleArea);
                } else {
                    detail.setSingleWindowArea(totalArea);
                    detail.setArea(totalArea);
                }
            }

            // 设置备注，包含从窗型区域提取的所有属性信息
            StringBuilder remarkBuilder = new StringBuilder();
            section.windowData.forEach((key, value) -> {
                if (remarkBuilder.length() > 0) {
                    remarkBuilder.append("; ");
                }
                remarkBuilder.append(key).append(": ").append(value);
            });
            detail.setRemark(remarkBuilder.toString());

            // 设置安装位置
            if (section.windowLocation != null && !section.windowLocation.isEmpty()) {
                detail.setInstallationLocation(section.windowLocation);
                detail.setInstallLocation(section.windowLocation);
            }

            // 获取订单编号
            String orderId = orderInfo.containsKey("orderNo") ? orderInfo.get("orderNo").toString() : null;
            String windowNo = detail.getWindowNo();

            // 提取图片信息
            try {
                // 通过遍历所有图片位置来查找与当前窗型相符的图片
                if (sheet instanceof XSSFSheet) {
                    XSSFSheet xssfSheet = (XSSFSheet) sheet;
                    if (xssfSheet.getDrawingPatriarch() != null) {
                        List<XSSFShape> shapes = xssfSheet.getDrawingPatriarch().getShapes();
                        for (XSSFShape shape : shapes) {
                            if (shape instanceof XSSFPicture) {
                                XSSFPicture picture = (XSSFPicture) shape;
                                XSSFClientAnchor anchor = picture.getPreferredSize();

                                // 判断图片是否在窗型区域内
                                if (anchor.getRow1() >= section.startRow - 5 &&
                                        anchor.getRow1() <= section.endRow + 5 &&
                                        anchor.getCol1() >= section.startCol - 5 &&
                                        anchor.getCol1() <= section.endCol + 5) {

                                    PictureData pictureData = picture.getPictureData();
                                    String photoUrl = saveImageFile(pictureData,
                                            detail.getId(),
                                            windowNo,
                                            orderId);

                                    if (photoUrl != null && !photoUrl.isEmpty()) {
                                        detail.setPhotoUrl(photoUrl);
                                        log.debug("窗型图片URL: {}", photoUrl);
                                        break;  // 找到了关联图片，可以退出循环
                                    }
                                }
                            }
                        }
                    }
                }
            } catch (Exception e) {
                log.error("提取窗型图片时发生错误", e);
            }

            // 如果没有找到图片，不设置photoUrl
            if (detail.getPhotoUrl() == null || detail.getPhotoUrl().isEmpty()) {
                detail.setPhotoUrl(null);
                log.debug("未找到有效图片，photoUrl设置为null");
            }

            // 使用订单基本信息填充其他字段
            if (orderInfo.containsKey("orderNo")) {
                detail.setOrderId(orderInfo.get("orderNo").toString());
            }

            // 如果没有系列信息，设置默认系列
            if (detail.getWindowSeries() == null || detail.getWindowSeries().isEmpty()) {
                detail.setWindowSeries("新格尔-默认系列");
            }

            // 如果没有颜色信息，设置默认颜色
            if (detail.getWindowColor() == null || detail.getWindowColor().isEmpty()) {
                detail.setWindowColor("默认色");
            }

            // 设置默认单价和金额
            if (detail.getUnitPrice() == null) {
                detail.setUnitPrice(BigDecimal.ZERO);
            }

            if (detail.getAmount() == null && detail.getUnitPrice() != null) {
                BigDecimal quantity = BigDecimal.valueOf(detail.getQuantity() != null ? detail.getQuantity() : 1);
                detail.setAmount(detail.getUnitPrice().multiply(quantity).setScale(2, RoundingMode.HALF_UP));
            }

            return detail;

        } catch (Exception e) {
            log.error("处理窗型区域时发生错误", e);
            return null;
        }
    }
    
    /**
     * 判断单元格是否为空
     */
    private boolean isEmptyCell(Cell cell) {
        return cell == null || cell.getCellType() == CellType.BLANK || 
               (cell.getCellType() == CellType.STRING && cell.getStringCellValue().trim().isEmpty());
    }
    
    /**
     * 获取单元格的字符串值
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 处理数字格式的单元格，避免科学计数法
                    double value = cell.getNumericCellValue();
                    if (value == Math.floor(value)) {
                        return String.valueOf((long) value);
                    } else {
                        return String.valueOf(value);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    try {
                        return cell.getStringCellValue();
                    } catch (Exception ex) {
                        return "";
                    }
                }
            default:
                return "";
        }
    }
    
    /**
     * 获取单元格的数值
     */
    private double getCellValueAsDouble(Cell cell) {
        if (cell == null) {
            return 0.0;
        }
        
        switch (cell.getCellType()) {
            case NUMERIC:
                return cell.getNumericCellValue();
            case STRING:
                try {
                    return Double.parseDouble(cell.getStringCellValue().trim());
                } catch (NumberFormatException e) {
                    return 0.0;
                }
            case BOOLEAN:
                return cell.getBooleanCellValue() ? 1.0 : 0.0;
            case FORMULA:
                try {
                    return cell.getNumericCellValue();
                } catch (Exception e) {
                    return 0.0;
                }
            default:
                return 0.0;
        }
    }

    /**
     * 解析窗号信息，提取窗号、数量和面积
     *
     * @param windowNoValue 窗号单元格值
     * @return 包含窗号、数量和面积的Map
     */
    private Map<String, Object> parseWindowNoInfo(String windowNoValue) {
        Map<String, Object> result = new HashMap<>();
        result.put("windowNo", "");
        result.put("quantity", 1);
        result.put("area", 0.0);

        if (windowNoValue == null || windowNoValue.isEmpty()) {
            return result;
        }

        try {
            // 1. 尝试使用格式: 窗号/数量/总面积
            Pattern fullPattern = Pattern.compile("(.*?)/(\\d+)(?:个|件|樘)?/(\\d+(?:\\.\\d+)?)(?:㎡|m²)");
            Matcher fullMatcher = fullPattern.matcher(windowNoValue);

            if (fullMatcher.find()) {
                // 窗号
                result.put("windowNo", fullMatcher.group(1).trim());

                // 数量
                try {
                    int quantity = Integer.parseInt(fullMatcher.group(2).trim());
                    result.put("quantity", quantity);
                } catch (NumberFormatException e) {
                    log.error("解析数量时发生错误: {}", fullMatcher.group(2), e);
                }

                // 总面积
                try {
                    double area = Double.parseDouble(fullMatcher.group(3).trim());
                    result.put("area", area);
                } catch (NumberFormatException e) {
                    log.error("解析面积时发生错误: {}", fullMatcher.group(3), e);
                }

                return result;
            }

            // 2. 如果不是完整格式，尝试提取窗号
            String numberPattern = "(?i)(C|窗|门)(\\d+)";
            Pattern pattern = Pattern.compile(numberPattern);
            Matcher matcher = pattern.matcher(windowNoValue);

            if (matcher.find()) {
                result.put("windowNo", matcher.group());
            } else {
                result.put("windowNo", windowNoValue);
            }

            // 3. 尝试提取面积
            Pattern areaPattern = Pattern.compile("(\\d+\\.\\d+)(?:㎡|m²)");
            Matcher areaMatcher = areaPattern.matcher(windowNoValue);
            if (areaMatcher.find()) {
                try {
                    double area = Double.parseDouble(areaMatcher.group(1));
                    result.put("area", area);
                } catch (NumberFormatException e) {
                    log.error("解析面积时发生错误: {}", areaMatcher.group(1), e);
                }
            }

            return result;
        } catch (Exception e) {
            log.error("解析窗号信息时发生错误: {}", windowNoValue, e);
            return result;
        }
    }
} 